import json
import base64
from io import StringIO
from typing import List
from Crypto.Cipher import DES
from Crypto.Random import get_random_bytes  
from Crypto.Util.Padding import pad, unpad 
from decimal import Decimal
from sqlalchemy import create_engine, Column, Integer, String,Numeric,BigInteger,SmallInteger, DateTime, Enum, SmallInteger, Boolean, ForeignKey, \
    select, func, update, delete, or_
from sqlalchemy.orm import declarative_base, relationship, backref, Session
from sqlalchemy.types import CHAR
from sqlalchemy.dialects.mysql import TINYINT
from sqlalchemy.sql import and_ 
from datetime import datetime
from enum import IntEnum
from Model.ResponseMode import ClientHisParam,ClientHisResponse
from typing import Optional

encryptKey = "@-8%!Was"
des = DES.new(encryptKey.encode('utf-8'), DES.MODE_ECB)  # 也可以使用DES.MODE_CBC，但需要提供一个IV（初始化向量） 
userName = 'xiaciadmin'
password = 'Pass#2024#'
host = '127.0.0.1'
port = 3406
database = 'xiaci'
engine = create_engine(f'mysql+pymysql://{userName}:{password}@{host}:{port}/{database}?charset=utf8', echo=True, future=True, isolation_level="READ UNCOMMITTED",
        pool_size=20,
        max_overflow=10)

Base = declarative_base()

class ClientHis(Base):  
    """监控记录表"""
    __tablename__ = 't_client_his'
    Id = Column(BigInteger, name='Id', primary_key=True)
    CClientName = Column(String(100), name='CClientName')
    CComName = Column(String(100), name='CComName')
    CRemarks = Column(String(500), name='CRemarks')
    CDay = Column(String(8), name='CDay')
    CCount = Column(Integer(), name='CCount')
    CCountA = Column(Integer(), name='CCountA')
    CCountB = Column(Integer(), name='CCountB')
    CCountC = Column(Integer(), name='CCountC')
    CCountD = Column(Integer(), name='CCountD')
    IsValid = Column(SmallInteger(), name='IsValid')
    CreaterName = Column(String(50), name='CreaterName')
    CreateTime = Column(DateTime, default=func.now(), nullable=False, name='CreateTime')


def CreateClientHis(itemIn: ClientHisParam):
    session = Session(bind=engine, future=True)
    try:  
        clientHis = session.query(ClientHis).filter(ClientHis.CClientName ==itemIn.ClientName,ClientHis.CComName == itemIn.ComName,ClientHis.CDay == itemIn.Day).first()
        if not clientHis:
            clientHis = ClientHis(CClientName = itemIn.ClientName,
                            CComName = itemIn.ComName,
                            CRemarks = itemIn.Remarks,
                            CDay = itemIn.Day,
                            CCount = itemIn.Count,
                            CCountA = itemIn.CountA,
                            CCountB = itemIn.CountB,
                            CCountC = itemIn.CountC ,
                            CCountD = itemIn.CountD,  
                            IsValid = 1,
                            CreaterName = '')
            session.add(clientHis)  
        else:
            clientHis.CRemarks = itemIn.Remarks
            clientHis.CCount = itemIn.Count
            clientHis.CCountA = itemIn.CountA
            clientHis.CCountB = itemIn.CountB
            clientHis.CCountC = itemIn.CountC
            clientHis.CCountD = itemIn.CountD
        session.commit()  
        session.refresh(clientHis) 
    except:
        return 0
    else:
        return clientHis.Id
    finally:  
        session.close()

def GetDefaultDay():  
    # 返回当前日期的字符串表示（格式为 YYYY-MM-DD）  
    return datetime.now().strftime('%Y-%m-%d') 

def SearchClientHis(param: ClientHisParam):
    session = Session(bind=engine, future=True)
    results = []
    if not (param.ClientName or param.ComName or param.Day):  
        return results 
    query_conditions = BuildQuery(param.ClientName, param.ComName, param.Day)
    #if query_conditions:  
    list = session.query(ClientHis).filter(query_conditions).order_by(ClientHis.CDay).all()
    for i in range(len(list)):
        item = list[i]
        p = ClientHisParam()
        p.ClientName = item.CClientName
        p.ComName = item.CComName
        p.Remarks = item.CRemarks
        p.Day = item.CDay
        p.Count = item.CCount
        p.CountA = item.CCountA
        p.CountB = item.CCountB
        p.CountC = item.CCountC
        p.CountD = item.CCountD
        results.append(p)
    return results


# 动态构建查询条件的函数  
def BuildQuery(client_name: Optional[str] = None, com_name: Optional[str] = None, day: Optional[str] = None):  
    query_conditions = []  
    if client_name:  
        query_conditions.append(ClientHis.CClientName.ilike(f'%{client_name}%')) 
    if com_name:  
        query_conditions.append(ClientHis.CComName.ilike(f'%{com_name}%')) 
    if day:  
        query_conditions.append(ClientHis.CDay == day)  
    return and_(*query_conditions) if query_conditions else None  

def DesEncrypt(plaintext):  
    """  
    DES加密函数  
    :param plaintext: 要加密的明文，必须是8的倍数长度（如果不是，需要填充）  
    :param key: 加密密钥，必须是8字节长  
    :return: 加密后的密文  
    """ 
    plaintextB = plaintext.encode('utf-8')
    # 如果明文长度不是8的倍数，则需要进行填充  
    if len(plaintextB) % 8 != 0:  
        plaintextB = pad(plaintextB, DES.block_size)  
    # 创建DES加密器  
    
    # 加密明文  
    ciphertext = des.encrypt(plaintextB)
    return base64.b64encode(ciphertext).decode('utf-8')  